package com.wgn.reportquery.dao;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.wgn.reportquery.cons.Const;
import com.wgn.reportquery.model.Reportdata;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ImportDaoImple {
	Connection conn = null;

	public ImportDaoImple() {

		String driver = "com.mysql.jdbc.Driver";
		// String url =
		// "jdbc:mysql://127.0.0.1:3306/reportdatabase?characterEncoding=utf-8";
		// String username = "root";
		// String password = "123";

		try {
			Class.forName(driver); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(Const.MYSQLURL,
					Const.dbusername, Const.dbpassword);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean exists(String sql, List<String> para) {
		boolean exists = false;
		PreparedStatement pstmt = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			if (null != para)
				for (int i = 0; i < para.size(); i++) {
					pstmt.setString(i + 1, para.get(i));
				}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				exists = true;
				break;

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return exists;
		}

	}

	public List<Reportdata> getAll(String sql) {
		List<Reportdata> datas = new ArrayList<Reportdata>();

		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Reportdata r = new Reportdata(rs.getString(1), rs.getString(2),
						rs.getString(4), rs.getString(5), rs.getString(3),
						rs.getString(6), rs.getString(9), rs.getString(8));
				datas.add(r);

			}
			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return datas;
	}

	public int insert(List<Reportdata> rs, String username) {

		int count = 0;
		String sql = "insert into DATAS (NO,INFO,REPORTDATE,VAL,SCOPE,REPORTNO,USERNAME,YCTS,DW) values(?,?,?,?,?,?,?,?,?)";

		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			final int batchSize = 1000;
			
			for (Reportdata r : rs) {
		
				pstmt.setString(1, r.getNo());
				pstmt.setString(2, r.getInfo());
				pstmt.setString(3, r.getReportdate());
				pstmt.setString(4, r.getVal());
				pstmt.setString(5, r.getScope());
				pstmt.setString(6, r.getReportNo());
				pstmt.setString(7, username);
				pstmt.setString(8, r.getJg());
				pstmt.setString(9, r.getDw());
				System.out.println(r.toString());
				pstmt.addBatch();
				if (++count % batchSize == 0) {
					pstmt.executeBatch();
				}
			}

			pstmt.executeBatch();
			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return count;
	}

	public int delete(String sql) {

		int i = 0;

		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();

			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public void close() {
		// TODO Auto-generated method stub
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private static String addHostpitallistSql = "INSERT INTO `reportdatabase`.`hostpitallist` (`HOSPITALVALUE`, `HOSPITALTITLE`) VALUES (?,?); ";
	private static String delHostpitallistSql = "DELETE FROM hostpitallist; ";

	public void updateHostpitalList(List<String> valuelist,
			List<String> titlelist) throws SQLException {

		PreparedStatement pstmt;
		pstmt = (PreparedStatement) conn.prepareStatement(delHostpitallistSql);
		pstmt.executeUpdate();
		pstmt.close();
		pstmt = (PreparedStatement) conn.prepareStatement(addHostpitallistSql);

		for (int i = 0; i < valuelist.size(); i++) {
			pstmt.setString(1, valuelist.get(i));
			pstmt.setString(2, titlelist.get(i));
			pstmt.addBatch();
		}

		pstmt.executeBatch();
		pstmt.close();

	}

	public void update(String sql, List<String> para) throws SQLException {
		// TODO Auto-generated method stub

		PreparedStatement pstmt = (PreparedStatement) conn
				.prepareStatement(sql);

		if (null != para) {

			for (int i = 0; i < para.size(); i++) {
				pstmt.setString(i + 1, para.get(i));
			}

		}

		pstmt.executeUpdate();

	}


}
